package vcp.server.DAO;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;

import vcp.entity.Subscription;
import vcp.entity.SubscriptionHistory;
import vcp.enums.SiteEnum;
import vcp.enums.SubscriptionType;
import vcp.server.DBAdapter;
import vcp.server.DAO.interfaces.IGenericDAO;
import vcp.server.utility.Constants.SubscriptionHistoryTable;
import vcp.server.utility.Constants.TableNames;
import vcp.server.utility.SQLQueries;

/**
 * <p>
 * subscription history table sql queries class
 * </p>
 * 
 * @author VCP team
 * 
 */
public class SubscriptionHistoryDAO implements IGenericDAO <SubscriptionHistory, Void>
{
	// ****************************************************************************************************************************
	// Public methods
	// ****************************************************************************************************************************
	public List <SubscriptionHistory> findAllCanceledSubscriptionsHistory ()
	{
		List <SubscriptionHistory> listOfSubscription = new ArrayList <>();

		String query =
				SQLQueries.SELECT_ALL
						+ SQLQueries.FROM + TableNames.SUBSCRIPTIONHISTORY
						+ " " + SQLQueries.WHERE
						+ SubscriptionHistoryTable.IS_CANCELED + "=" + 1;

		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			while ( set.next() )
			{
				SubscriptionHistory subscriptionHistory = this.create();

				subscriptionHistory.setSubscriptionID(set.getInt(SubscriptionHistoryTable.ID));
				subscriptionHistory.setCustomerID(set.getInt(SubscriptionHistoryTable.CUSTOMER_ID));
				subscriptionHistory.setSite(SiteEnum.convert(set.getString(SubscriptionHistoryTable.SITE_NAME)));
				subscriptionHistory.setSubscriptionType(SubscriptionType.convert(set.getString(SubscriptionHistoryTable.TYPE)));
				subscriptionHistory.setStartDate(new DateTime(set.getTimestamp(SubscriptionHistoryTable.START_DATE)));
				subscriptionHistory.setEndDate(new DateTime(set.getTimestamp(SubscriptionHistoryTable.END_DATE)));
				subscriptionHistory.setArrivalDate(new DateTime(set.getTimestamp(SubscriptionHistoryTable.ARRIVAL_DATE)));
				subscriptionHistory.setLeaveDate(new DateTime(set.getTimestamp(SubscriptionHistoryTable.LEAVE_DATE)));
				subscriptionHistory.setCanceld(set.getBoolean(SubscriptionHistoryTable.IS_CANCELED));

				listOfSubscription.add(subscriptionHistory);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return listOfSubscription;
	}

	/**
	 * finds all subscription where leave date equals to {@code leaveDate}
	 * 
	 * @param leaveDate
	 * @param siteEnum
	 * @return list of subscription in history by leave date
	 */
	public List <SubscriptionHistory> findAllSubscriptionsHistoryByLeaveDate ( DateTime leaveDate, SiteEnum siteEnum )
	{
		List <SubscriptionHistory> listOfSubscription = new ArrayList <>();

		String query =
				SQLQueries.SELECT_ALL
						+ SQLQueries.FROM + TableNames.SUBSCRIPTIONHISTORY
						+ " " + SQLQueries.WHERE
						+ SQLQueries.DATE + "(" + SubscriptionHistoryTable.LEAVE_DATE + ")"
						+ "="
						+ "\"" + DateTimeFormat.forPattern("yyyy-MM-dd").print(leaveDate) + "\""
						+ " " + SQLQueries.AND
						+ SubscriptionHistoryTable.SITE_NAME + "=" + "\"" + siteEnum.toString() + "\"";

		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			while ( set.next() )
			{
				SubscriptionHistory subscriptionHistory = this.create();

				subscriptionHistory.setSubscriptionID(set.getInt(SubscriptionHistoryTable.ID));
				subscriptionHistory.setCustomerID(set.getInt(SubscriptionHistoryTable.CUSTOMER_ID));
				subscriptionHistory.setSite(SiteEnum.convert(set.getString(SubscriptionHistoryTable.SITE_NAME)));
				subscriptionHistory.setSubscriptionType(SubscriptionType.convert(set.getString(SubscriptionHistoryTable.TYPE)));
				subscriptionHistory.setStartDate(new DateTime(set.getTimestamp(SubscriptionHistoryTable.START_DATE)));
				subscriptionHistory.setEndDate(new DateTime(set.getTimestamp(SubscriptionHistoryTable.END_DATE)));
				subscriptionHistory.setArrivalDate(new DateTime(set.getTimestamp(SubscriptionHistoryTable.ARRIVAL_DATE)));
				subscriptionHistory.setLeaveDate(new DateTime(set.getTimestamp(SubscriptionHistoryTable.LEAVE_DATE)));
				subscriptionHistory.setCanceld(set.getBoolean(SubscriptionHistoryTable.IS_CANCELED));

				listOfSubscription.add(subscriptionHistory);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return listOfSubscription;
	}

	/**
	 * finds all subscriptions between start and end dates
	 * 
	 * @param startDate
	 * @param endDate
	 * @param siteEnum
	 * @return list of subscriptions
	 */
	public List <SubscriptionHistory> findAllSubscriptionsHistoryByDatesPeriodForSite ( DateTime startDate, DateTime endDate, SiteEnum siteEnum )
	{
		List <SubscriptionHistory> listOfSubscription = new ArrayList <>();

		String query =
				SQLQueries.SELECT_ALL
						+ SQLQueries.FROM + TableNames.SUBSCRIPTIONHISTORY
						+ " " + SQLQueries.WHERE
						+ SubscriptionHistoryTable.SITE_NAME + "=" + "\"" + siteEnum.toString() + "\""
						+ " " + SQLQueries.OR
						+ SubscriptionHistoryTable.SITE_NAME + " is NULL"
						+ " " + SQLQueries.AND
						+ SQLQueries.DATE + "(" + SubscriptionHistoryTable.START_DATE + ")"
						+ SQLQueries.GTE
						+ "\"" + DateTimeFormat.forPattern("yyyy-MM-dd").print(startDate) + "\""
						+ " " + SQLQueries.AND
						+ SQLQueries.DATE + "(" + SubscriptionHistoryTable.END_DATE + ")"
						+ SQLQueries.LTE
						+ "\"" + DateTimeFormat.forPattern("yyyy-MM-dd").print(endDate) + "\"";

		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			while ( set.next() )
			{
				SubscriptionHistory subscriptionHistory = this.create();

				subscriptionHistory.setSubscriptionID(set.getInt(SubscriptionHistoryTable.ID));
				subscriptionHistory.setCustomerID(set.getInt(SubscriptionHistoryTable.CUSTOMER_ID));
				subscriptionHistory.setSite(SiteEnum.convert(set.getString(SubscriptionHistoryTable.SITE_NAME)));
				subscriptionHistory.setSubscriptionType(SubscriptionType.convert(set.getString(SubscriptionHistoryTable.TYPE)));
				subscriptionHistory.setStartDate(new DateTime(set.getTimestamp(SubscriptionHistoryTable.START_DATE)));
				subscriptionHistory.setEndDate(new DateTime(set.getTimestamp(SubscriptionHistoryTable.END_DATE)));
				subscriptionHistory.setArrivalDate(new DateTime(set.getTimestamp(SubscriptionHistoryTable.ARRIVAL_DATE)));
				subscriptionHistory.setLeaveDate(new DateTime(set.getTimestamp(SubscriptionHistoryTable.LEAVE_DATE)));
				subscriptionHistory.setCanceld(set.getBoolean(SubscriptionHistoryTable.IS_CANCELED));

				listOfSubscription.add(subscriptionHistory);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return listOfSubscription;
	}

	/**
	 * archives subscription into instance of subscription history class
	 * 
	 * @param subscription
	 * @return class
	 */
	public SubscriptionHistory createFromSubscription ( Subscription subscription )
	{
		SubscriptionHistory subscriptionHistory = this.create();
		subscriptionHistory.setSubscriptionID(subscription.getSubscribtionID());
		subscriptionHistory.setCustomerID(subscription.getCustomerID());
		subscriptionHistory.setSite(subscription.getSite());
		subscriptionHistory.setSubscriptionType(subscription.getSubscriptionType());
		subscriptionHistory.setStartDate(subscription.getStartDate());
		subscriptionHistory.setEndDate(subscription.getEndDate());
		subscriptionHistory.setArrivalDate(subscription.getArrivalDate());

		return subscriptionHistory;
	}

	/**
	 * Not implemented
	 */
	@Override
	public Integer findMaxID ()
	{
		return null;
	}

	@Override
	public SubscriptionHistory create ()
	{
		return new SubscriptionHistory();
	}

	/**
	 * Not implemented
	 */
	@Override
	public SubscriptionHistory findByEntity ( Void entity )
	{
		return null;
	}

	/**
	 * Not implemented
	 */
	@Override
	public SubscriptionHistory findByID ( int ID )
	{
		return null;
	}

	/**
	 * Not implemented
	 */
	@Override
	public SubscriptionHistory findByID ( String ID )
	{
		return null;
	}

	/**
	 * Not implemented
	 */
	@Override
	public List <SubscriptionHistory> findAll ( Void info )
	{
		return null;
	}

	@Override
	public boolean save ( SubscriptionHistory entity )
	{
		boolean result = false;

		String valuesSet =
				String.format(
					"%d,%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",%d"
					, entity.getSubscriptionID()
					, entity.getCustomerID()
					, entity.getSite().toString()
					, entity.getSubscriptionType().toString()
					, entity.getStartDate() != null
													? new Timestamp(entity.getStartDate().getMillis())
													: new Timestamp(new DateTime(2001, 1, 1, 0, 0, 0).getMillis())
					, entity.getEndDate() != null
													? new Timestamp(entity.getEndDate().getMillis())
													: new Timestamp(new DateTime(2001, 1, 1, 0, 0, 0).getMillis())
					, entity.getArrivalDate() != null
														? new Timestamp(entity.getArrivalDate().getMillis())
														: new Timestamp(new DateTime(2001, 1, 1, 0, 0, 0).getMillis())
					, entity.getLeaveDate() != null
													? new Timestamp(entity.getLeaveDate().getMillis())
													: new Timestamp(new DateTime(2001, 1, 1, 0, 0, 0).getMillis())
					, entity.isCanceld()
										? 1
										: 0);

		String sqlQuery =
				String.format(
					SQLQueries.INSERT_INTO__TABLENAME__VALUES__
					, TableNames.SUBSCRIPTIONHISTORY
					, valuesSet);

		try
		{
			result = DBAdapter.getAdapter().save(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return result;
	}

	/**
	 * Not implemented
	 */
	@Override
	public boolean refresh ( String sqlQuery )
	{
		return false;
	}

	/**
	 * Not implemented
	 */
	@Override
	public boolean deleteByID ( int ID )
	{
		return false;
	}

	/**
	 * Not implemented
	 */
	@Override
	public boolean deleteByID ( String ID )
	{
		return false;
	}
}
